﻿CREATE PROCEDURE [acms].[Post_Put]
	@Id int, 
	@SiteId int, 
	@PostGuid uniqueidentifier, 
	@ParentGuid uniqueidentifier, 
	@Name varchar(100), 
	@DisplayName varchar(100), 
	@TemplateId int, 
	@ThumbnailId int,
	@Summary varchar(2000), 
	@Sequence int, 
	@PublishFrom DateTime, 
	@PublishTo DateTime, 
	@Keywords varchar(2000),
	@Notes nvarchar(2000),
	@RequiresPublishing bit,
	@ReadyToPublish bit,
	@CreatedDateTime datetime,
	@DeveloperLocked bit,
	@ConcurencyId timestamp,
	@IdOut int output, 
	@SiteNameOut varchar(50) output, 
	@ParentPathOut varchar(255) output, 
	@TemplateNameOut varchar(255) output, 
	@SequenceOut int output, 
	@ConcurencyIdOut timestamp output
AS

	declare @RequiredPositionUpdate bit
	declare @RequiredChildPathUpdate bit
	set @RequiredPositionUpdate = 0
	set @RequiredChildPathUpdate = 0
	
	if exists (select null from Post_View with (nolock) where Id = @Id) begin
		
		--if parentGuid has changed need to update parent path for its self and on all children
		if not exists (select null from Post_View with (nolock) where Id = @Id and ParentGuid = @ParentGuid) begin
			set @RequiredPositionUpdate = 1
		end
		--if name has changed update parent path for all children
		else if not exists (select null from Post_View with (nolock) where Id = @Id and Name = @Name) begin
			set @RequiredChildPathUpdate = 1
		end

		
		update [Posts]
		set
			SiteId = @SiteId,
			ParentGuid = @ParentGuid,
			Name = @Name,
			DisplayName = @DisplayName,
			TemplateId = @TemplateId,
			ThumbnailId = @ThumbnailId,
			Summary = @Summary,
			Keywords = @Keywords,
			Sequence = @Sequence,
			PublishFrom = @PublishFrom,
			PublishTo = @PublishTo,
			RequiresPublishing = @RequiresPublishing,
			ReadyToPublish = @ReadyToPublish,
			Notes = @Notes,
			DeveloperLocked = @DeveloperLocked
		where
			Id = @Id
			and VersionId = @ConcurencyId
		if @@RowCount <> 1 
			return -100
		else
			set @IdOut = @Id
		
	end
	else if @Id is null or @Id = 0 begin
		if(@PublishFrom is null) begin
			select @Sequence = isNull(Max(a.Sequence) + 1, 0) 
			from Post_View a with (nolock) 
				inner join PostPositions b with(nolock) on a.Id = b.PostId and b.PublishDate is null
			where ParentGuid = @ParentGuid	
		end
		else begin
			select @Sequence = isNull(Max(a.Sequence) + 1, 0) 
			from Post_View a with (nolock) 
				inner join PostPositions b with(nolock) on a.Id = b.PostId and b.PublishDate >= @PublishFrom and b.PublishDate < isNull(@PublishTo, '9999/12/31')
			where ParentGuid = @ParentGuid	
		end

		select @Sequence = isNull(Max(Post_View.Sequence) + 1, 0) 
		from Post_View with (nolock) 
			inner join PostPositions with(nolock) on Post_View.Id = PostPositions.PostId and PostPositions.PublishDate is null
		where ParentGuid = @ParentGuid	
		
		insert [Posts]
			(SiteId, PostGuid, ParentGuid, Name, DisplayName, Sequence, TemplateId, ThumbnailId, Summary, Keywords, PublishFrom, PublishTo, RequiresPublishing, ReadyToPublish, Notes, CreatedDateTime, DeveloperLocked)
		values
			(@SiteId, @PostGuid, @ParentGuid, @Name, @DisplayName, @Sequence, @TemplateId, @ThumbnailId, @Summary, @Keywords, @PublishFrom, @PublishTo, @RequiresPublishing, @ReadyToPublish, @Notes, @CreatedDateTime, @DeveloperLocked)
		
		set @IdOut = @@Identity
		
		insert into PostPositions
			(PostId, PublishDate)
		values
			(@IdOut, @PublishFrom)
	end
	else begin
		return -200
	end
	
	--if there are any posts overlapping set their end date and remove postsions outside range
	if @PublishFrom is not null and Exists(select null from Post_View with(nolock) where PostGuid = @PostGuid and PublishFrom is not null and IsNull(PublishTo, '9999/12/31') > @PublishFrom) begin
		update Post_View Set PublishTo = @PublishFrom
		Where PostGuid = @PostGuid
			and PublishFrom is not null 
			and IsNull(PublishTo, '9999/12/31') > @PublishFrom
			and Id <> @IdOut
		
		--delete out any unnessary post positions
		delete acms.PostPositions
		from acms.PostPositions a
			inner join acms.Posts b on a.PostId = b.Id
		where b.Id <> @Id and b.PostGuid = @PostGuid and a.PublishDate >= b.PublishTo
	end
	
	
	--Deleted any PostPositions out of range
	if(@PublishFrom is not null) begin
		delete PostPositions where PostId = @IdOut and PublishDate < @PublishFrom
		delete PostPositions where PostId = @IdOut and PublishDate is null
	end
	else begin
		delete PostPositions where PostId = @IdOut and PublishDate is not null
	end

	if(@PublishTo is not null) begin
		delete PostPositions where PostId = @IdOut and PublishDate >= @PublishTo
	end
	
	--Check there is a postposition publish date for this @PublishFrom
	if @PublishFrom is not null and not exists(select null from PostPositions with(nolock) where isNull(PublishDate, '9999/12/31') = isNull(@PublishFrom, '9999/12/31')) begin
		exec acms.PostPosition_BuildTree null, @PublishFrom
	end
	else if @PublishFrom is null and not exists(select null from PostPositions with(nolock) where PublishDate is null) begin
		insert into PostPositions
			(PostId, PublishDate)
		select Id, null
		From Posts with(nolock)
		where PublishFrom is null
	end

	--Check there is a postposition publish date for this @PublishTo
	if @PublishTo is not null and not exists(select null from PostPositions with(nolock) where isNull(PublishDate, '9999/12/31') = isNull(@PublishTo, '9999/12/31')) begin
		exec acms.PostPosition_BuildTree null, @PublishTo
	end
	
	--update any postpositions records that exist for this posts if either parentguid or sequence
	if(@RequiredPositionUpdate = 1) begin
		update PostPositions
		set ParentPath = [acms].[GetPostParentPath](PostId, PublishDate),
			ParentSequence = [acms].[GetPostParentSequence](PostId, PublishDate)
		where PostId = @IdOut
	end
	
	--update parent path properties
	/*if @RequiredChildPathUpdate = 1
	begin
		update PostPositions 
		set ParentPath = [acms].[GetPostParentPath](PostId, PublishDate)
		from PostPositions a with(nolock) 
			inner join Posts b with(nolock) on a.PostId = b.Id
		where b.ParentGuid = @PostGuid
			and isNull(a.PublishDate, '9999/12/31') >= isNull(@PublishFrom, '9999/12/31') and isNull(a.PublishDate, '9999/12/31 23:00:00') < isNull(@PublishTo, '9999/12/31 23:59:00')
	end*/

	
	--check there is a record for each post posision it overlaps
	insert into PostPositions
		(PostId, PublishDate)
	select distinct @IdOut, PublishDate 
	From PostPositions with(nolock)
	where PublishDate >= @PublishFrom 
		and PublishDate < isNull(@PublishTo, '9999/12/31')
		and PublishDate not in (Select PublishDate From	PostPositions with(nolock) where PostId = @IdOut)

	--check there is a pending post if not create it
	if @PublishFrom is not null and not exists (select null from Post_View with (nolock) where PostGuid = @PostGuid and PublishFrom is null) begin
		declare @newPendingId int
		insert into Posts
			(SiteId, PostGuid, ParentGuid, Name, DisplayName, ThumbnailId, TemplateId, Summary, Keywords, Sequence, RequiresPublishing, ReadyToPublish, Notes)
		values
			(@SiteId, @PostGuid, @ParentGuid, @Name, @DisplayName, @ThumbnailId, @TemplateId, @Summary, @Keywords, @Sequence, 0, 0, @Notes)
		set @newPendingId = @@identity
		
		insert into PostPositions
			(PostId, PublishDate)
		values
			(@newPendingId, null)
		
		insert into PostMenu
			(MenuId, PostId, Included)
		select MenuId, @newPendingId, Included
		from PostMenu with(nolock)
		where PostId = @IdOut and deleted = 0

		insert into PostControls
			(PostId, TemplateControlId, Value)
		select @newPendingId, TemplateControlId, Value
		from PostControls with(nolock)
		where PostId = @IdOut and deleted = 0

		insert into PostTopics
			(PostId, TopicId)
		select @newPendingId, TopicId
		from PostTopics with(nolock)
		where PostId = @IdOut and deleted = 0
	end

	
	select 
		@SiteNameOut = a.SiteName,
		@TemplateNameOut = a.TemplateName,
		@SequenceOut = a.Sequence,
		@ConcurencyIdOut = a.VersionId,
		@ParentPathOut = b.ParentPath
	from Post_View a with (nolock)
		inner join acms.PostPositions b with (nolock) on a.Id = b.PostId
	where a.Id = @IdOut
		and IsNull(b.PublishDate, '9999/12/31') = IsNull(@PublishFrom, '9999/12/31')
RETURN 0;